
package com.cari.baju.yog.database;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;

import com.cari.baju.yog.MainActivity;
import com.cari.baju.yog.entity.E_Category;
import com.cari.baju.yog.entity.E_Toko;
import com.cari.baju.yog.utils.Utils;
import com.google.android.gms.maps.model.LatLng;

public class Database extends SQLiteOpenHelper {
    private static final String DB_NAME = "toko_baju";
    private static final int DB_VERSION = 1;

    private static Database instance;
    private static SQLiteDatabase db;

    private static final String TB_CATEGORY = "tb_category";
    private static final String TB_CATEGORY_ID = "_id";
    private static final String TB_CATEGORY_NAME = "name";

    private static final String TB_TOKO = "tb_toko";
    private static final String TB_TOKO_ID = "_id";
    private static final String TB_TOKO_NAME = "name";
    private static final String TB_TOKO_CATEGORY = "category";
    private static final String TB_TOKO_ADDRESS = "address";
    private static final String TB_TOKO_PHONE = "phone";
    private static final String TB_TOKO_LAT = "lat";
    private static final String TB_TOKO_LNG = "lng";
    private static final String TB_TOKO_TIMESTAMP = "timestamp";

    private static final String CREATE_CATEGORY = "CREATE TABLE " + TB_CATEGORY
            + "( "
            + TB_CATEGORY_ID + " integer primary key,"
            + TB_CATEGORY_NAME + " text);";

    private static final String CREATE_TOKO = "CREATE TABLE " + TB_TOKO
            + "( "
            + TB_TOKO_ID + " integer primary key,"
            + TB_TOKO_NAME + " text,"
            + TB_TOKO_CATEGORY + " text,"
            + TB_TOKO_ADDRESS + " text,"
            + TB_TOKO_PHONE + " text,"
            + TB_TOKO_LAT + " REAL,"
            + TB_TOKO_LNG + " REAL,"
            + TB_TOKO_TIMESTAMP + " integer);";

    private Database(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    public static Database getInstance(Context context) {
        if (instance == null) {
            instance = new Database(context);
            db = instance.getWritableDatabase();
        }
        return instance;
    }

    @Override
    public synchronized void close() {
        super.close();
        if (instance != null)
            instance.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(CREATE_CATEGORY);
        db.execSQL(CREATE_TOKO);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXIST " + TB_CATEGORY);
        db.execSQL("DROP TABLE IF EXIST " + TB_TOKO);

        onCreate(db);
    }

    public void insertCategory(E_Category category) {
        ContentValues values = new ContentValues();
        values.put(TB_CATEGORY_ID, category.getId());
        values.put(TB_CATEGORY_NAME, category.getName());

        long id = db.insert(TB_CATEGORY, null, values);
        Utils.TRACE("Database", "insertCategory : " + id);
    }

    public void insertToko(E_Toko toko) {
        ContentValues values = new ContentValues();
        values.put(TB_TOKO_ID, toko.getId());
        values.put(TB_TOKO_NAME, toko.getName());
        values.put(TB_TOKO_ADDRESS, toko.getAddress());
        values.put(TB_TOKO_PHONE, toko.getPhone());
        values.put(TB_TOKO_CATEGORY, toko.getCategory());
        values.put(TB_TOKO_LAT, toko.getLat());
        values.put(TB_TOKO_LNG, toko.getLng());
        values.put(TB_TOKO_TIMESTAMP, toko.getTimestamp());

        long id = db.insert(TB_TOKO, null, values);
        Utils.TRACE("Database", "insertToko : " + toko.getName());
    }

    public void updateToko(E_Toko toko) {
        ContentValues values = new ContentValues();
        values.put(TB_TOKO_ID, toko.getId());
        values.put(TB_TOKO_NAME, toko.getName());
        values.put(TB_TOKO_ADDRESS, toko.getAddress());
        values.put(TB_TOKO_PHONE, toko.getPhone());
        values.put(TB_TOKO_CATEGORY, toko.getCategory());
        values.put(TB_TOKO_LAT, toko.getLat());
        values.put(TB_TOKO_LNG, toko.getLng());
        values.put(TB_TOKO_TIMESTAMP, toko.getTimestamp());

        int i = db.update(TB_TOKO, values, TB_TOKO_ID + "=?", new String[] {
                String.valueOf(toko.getId())
        });
        Utils.TRACE("Database", "updateToko : " + toko.getName());
    }

    public E_Toko getToko(int id) {
        Cursor cursor = db.query(TB_TOKO, new String[] {
                TB_TOKO_ID,
                TB_TOKO_CATEGORY,
                TB_TOKO_NAME,
                TB_TOKO_ADDRESS,
                TB_TOKO_LAT,
                TB_TOKO_LNG,
                TB_TOKO_PHONE,
                TB_TOKO_TIMESTAMP
        },
                TB_TOKO_ID + "=?",
                new String[] {
                    String.valueOf(id)
                }, null, null, null);
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            return new E_Toko(cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_ID)),
                    cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_CATEGORY)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_NAME)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_ADDRESS)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_PHONE)),
                    cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LAT)),
                    cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LNG)),
                    cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_TIMESTAMP)));
        } else {
            return null;
        }

    }

    public List<E_Toko> searchToko(String search, int category, Location currentLocation) {
        List<E_Toko> listToko = new ArrayList<E_Toko>();
        Cursor cursor = null;
        if (category == 0) {
            cursor = db.rawQuery("SELECT * FROM " + TB_TOKO + " WHERE " + TB_TOKO_NAME
                    + " like '%" + search + "%'", null);
        } else {
            switch (category) {
                case MainActivity.CATEGORY_BATIK:
                    cursor = db.rawQuery("SELECT * FROM " + TB_TOKO + " WHERE category="
                            + MainActivity.CATEGORY_BATIK + " AND "
                            + TB_TOKO_NAME
                            + " like '%" + search + "%'", null);
                    break;

                case MainActivity.CATEGORY_MUSLIM:
                    cursor = db.rawQuery("SELECT * FROM " + TB_TOKO + " WHERE category="
                            + MainActivity.CATEGORY_MUSLIM + " AND "
                            + TB_TOKO_NAME
                            + " like '%" + search + "%'", null);
                    break;
                case MainActivity.CATEGORY_CASUAL:
                    cursor = db.rawQuery("SELECT * FROM " + TB_TOKO + " WHERE category="
                            + MainActivity.CATEGORY_CASUAL + " AND "
                            + TB_TOKO_NAME
                            + " like '%" + search + "%'", null);
                    break;

            }
        }

        Utils.TRACE("Database", "searchToko : cari : " + search);
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();

            do {

                double jarak = Utils.hitungJarak(
                        new LatLng(currentLocation.getLatitude(), currentLocation.getLongitude()),
                        new LatLng(cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LAT)),
                                cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LNG))));
                jarak = jarak / 1000;

                listToko.add(new E_Toko(
                        cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_ID)),
                        cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_CATEGORY)),
                        cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_ADDRESS)),
                        cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_PHONE)),
                        cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LAT)),
                        cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LNG)),
                        cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_TIMESTAMP)),
                        jarak)
                        );
            } while (cursor.moveToNext());
            sortingLocation(listToko);
            return listToko;
        } else {
            return null;
        }
    }

    private static void sortingLocation(List<E_Toko> list)
    {
        Collections.sort(list, new Comparator<E_Toko>() {
            @Override
            public int compare(E_Toko lhs, E_Toko rhs)
            {
                return (lhs.getJarak() < rhs.getJarak() ? -1
                        : (rhs.getJarak() == lhs.getJarak() ? 0 : 1));
            }
        });
    }

    public List<E_Toko> getTokoByCategory(int category) {
        Utils.TRACE("Database", "getTokoByCategory : " + category);
        List<E_Toko> listToko = new ArrayList<E_Toko>();
        Cursor cursor = db.query(TB_TOKO, new String[] {
                TB_TOKO_ID,
                TB_TOKO_NAME,
                TB_TOKO_CATEGORY,
                TB_TOKO_ADDRESS,
                TB_TOKO_LAT,
                TB_TOKO_LNG,
                TB_TOKO_PHONE,
                TB_TOKO_TIMESTAMP
        }, TB_TOKO_CATEGORY + "=?", new String[] {
                String.valueOf(category)
        }, null, null, null);

        if (cursor.getCount() > 0)
            cursor.moveToFirst();
        do {
            listToko.add(new E_Toko(
                    cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_ID)),
                    cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_CATEGORY)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_NAME)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_ADDRESS)),
                    cursor.getString(cursor.getColumnIndexOrThrow(TB_TOKO_PHONE)),
                    cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LAT)),
                    cursor.getDouble(cursor.getColumnIndexOrThrow(TB_TOKO_LNG)),
                    cursor.getInt(cursor.getColumnIndexOrThrow(TB_TOKO_TIMESTAMP))));
        } while (cursor.moveToNext());
        return listToko;
    }
}
